package cn.com.nes.site.module.dao.crm;

import cn.com.nes.common.util.*;
import cn.com.nes.site.entity.DataReq;
import cn.com.nes.site.entity.PageData;
import cn.com.nes.site.module.dao.BaseDaoImpl;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.ObjectUtil;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.TransactionStatus;
import org.springframework.web.multipart.MultipartFile;

import java.net.URLDecoder;
import java.text.SimpleDateFormat;
import java.util.*;

@Repository
public class CustomerDaoImpl extends BaseDaoImpl{
	private TransactionStatus newg_status = null;
	
	public PageData getCustomerList(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取客户列表失败");
        try {
            StringBuffer sb = new StringBuffer();
            StringBuffer sbfrom = new StringBuffer() ;
            sb.append("select a.uuid,a.customername,concat(if((a.customername is null or a.customername=''),'无',a.customername),'(',if((a.cluecompany is null or a.cluecompany=''),'无',a.cluecompany),')') nameAndCompany,a.address,a.personincharge,a.customerlevel,d.`name` customerlevelName, " +
            		" a.customernature,b.`name` customernatureName,a.businesstype,c.`name` businesstypeName, " +
            		" a.phone,a.createTime, " +
            		" (select count(1)from opportunity oppo where oppo.isdelete=0 and oppo.customerId=a.uuid ) oppoCount " +
					" from customer a " +
            		" left join t_param b on a.customernature=b.`value` and b.param_type='20' " +
            		" left join t_param c on a.businesstype=c.`value` and c.param_type='21' " +
            		" left join t_param d on a.customerlevel=d.`value` and d.param_type='22' " +
            		" where a.customername <>'' and a.customername is not null and a.isdelete=0 and a.company_id like concat('"+req.getUinfo().getCompanyId()+"','%') ");
            if (!StringUtils.isEmpty((String) param.get("comquery"))){
                sb.append(" and (").append(" a.customername like '%").append(param.get("comquery")).append("%' ");
                sb.append(" or a.phone='").append(param.get("comquery")).append("' ");
                sb.append(" or a.address like '%").append(param.get("comquery")).append("%' ");
                sb.append(" or a.personincharge like '%").append(param.get("comquery")).append("%') ");
            }
            if(!StringUtils.isEmpty((String) param.get("customerlevel"))){
                sb.append(" and a.customerlevel='").append(param.get("customerlevel")).append("' ");
            }
            if (!StringUtils.isEmpty((String) param.get("customernature"))){
                sb.append(" and a.customernature='").append(param.get("customernature")).append("' ");
            }
            if (!StringUtils.isEmpty((String) param.get("businesstype"))){
                sb.append(" and a.businesstype='").append(param.get("businesstype")).append("' ");
            }
            
            //工作台数据概览跳转查询
            if(!StringUtils.isEmpty((String) req.getReqData().get("dateType"))){
            	String dateType = String.valueOf(req.getReqData().get("dateType")) ;
            	String query = dateTypeCondition(dateType);
            	sb.append(query);
			}
            
            //工作台客户状态跳转
            if(!StringUtils.isEmpty((String) param.get("wb_zt"))) {
            	if("01".equals((String) param.get("wb_zt"))) {
            		StringBuffer sbform = new StringBuffer();
            		sbform.append(" select distinct a.uuid from customer a inner join customer_staff b on a.uuid=b.customer_id where a.isdelete=0 and b.deleted=0 and a.company_id like concat('"+req.getUinfo().getCompanyId()+"','%') ");
            		sb.append(" and a.uuid in(").append(sbform).append(") ");
            	}
            }
            
            //工作台待办跳转
            if(!StringUtils.isEmpty((String) param.get("wb_db"))) {
            	if("01".equals(param.get("wb_db"))) {
            		sb.append(" and a.uuid in (");
            		sb.append("select a.customerId from log_claim a where a.isdelete=0 and a.type='02' and a.userid='")
            	            .append(req.getUinfo().getId()).append("' ")
            				.append(" group by a.customerId HAVING max(a.modifyTime)<date_sub(now(),INTERVAL 30 day) ");
            		sb.append(")");
            	}
            }
            
            //行政区划
			if(!StringUtils.isEmpty((String) param.get("xzqhdm")) && !"86".equals(param.get("xzqhdm"))) {
				sb.append("  and a.xzqhdm like  '").append(XzqhUtil.getQueryXzqh(this.getJdbcTemplate_newg(),(String)req.getReqData().get("xzqhdm"))).append("%' ");
			}
            sb.append("  order by a.createTime desc ");
            sb = DBUtils.sql_check(sb) ;
            sbfrom.append(sb.toString()) ;
            int idx=sbfrom.indexOf(" from ") ;
            sbfrom.delete(0, idx);
            Page page = new Page(this.getJdbcTemplate_newg(),sbfrom.toString(),req.getPageid(),req.getPagesize(),res,new Object[]{}) ;
            sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize()) ;
            List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});

			// 处理 nameAndCompany
			if (CollectionUtil.isNotEmpty(list)) {
				for (Map<String, Object> map : list) {
					if (ObjectUtil.isNull(map)) continue;
					Object nameAndCompany = map.get("nameAndCompany");
					if (ObjectUtil.isNotNull(nameAndCompany) && String.valueOf(nameAndCompany).contains("(无)")) {
						map.put("nameAndCompany", map.get("customername"));
					}
				}
			}

            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }
	
	public PageData saveCustomer(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		newg_status = begin_newg();// 开启事物
		param.put("tableName", "customer");
		param.put("createUid",req.getUinfo().getId());
        param.put("isdelete","0");
        param.put("companyId", req.getUinfo().getCompanyId());
		
		//添加操作日志
		param.put("OperationLogType", "02");
		param.put("OperationLogId", "updateCustomerId");
		
		if(StringUtils.isEmpty((String) param.get("registeredcapital"))){
			param.put("registeredcapital",null);
		}
		if(StringUtils.isEmpty((String) param.get("establishmenttime"))) {
			param.remove("establishmenttime");
		}
		try {
			//拓展字段用
			String jsondata = (String) param.get("jsondata") ;
			jsondata =  URLDecoder.decode(jsondata, "UTF-8");
			param.put("jsondata", jsondata);
			//拓展字段用
			
			String uuid=(String)param.get("uuid");
			if(StringUtils.isEmpty(uuid)) {
				param.put("OperationLogName", "增加客户");
				param.put("OperationMemo", req.getUinfo().getNickName()+"添加了"+param.get("customername")+"客户");
				res =  insertTableData(newg_status, req) ;
			}else {
				param.put("keys", "uuid");
				param.put("OperationLogName", "修改客户");
				param.put("OperationMemo", req.getUinfo().getNickName()+"修改了"+param.get("customername")+"客户信息");
				res =  updateTableData(newg_status, req) ;
			}
			
			if(res.getRetCode()==0) {
				commit_newg(newg_status);
				res.setRetCode(0);
				res.setRetMessage("OK");
				res.setKey((String)param.get("uuid")) ;
			}else{
				rollback_newg(newg_status);
				res.setRetCode(9);
				res.setRetMessage("error");
				
			}
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
		} finally {
			
		}
		return res;
	}
	
	public PageData getCustomerDetail(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        Map<String,Object> extData = new LinkedHashMap<String, Object>();
        res.setRetCode(9);
        res.setRetMessage("获取客户信息失败");
        try {
            StringBuffer sb = new StringBuffer();
            //获取客户内容
            sb.delete(0,sb.length());
            sb.append("select a.jsondata,a.templateId,a.uuid,a.customernature,b.`name` customernatureName,a.businesstype,c.`name` businesstypeName, " +
            		" a.customerlevel,d.`name` customerlevelName,a.superiorcompany,a.customername,a.xzqhdm,e.xzqhmc,a.address,a.personincharge,a.phone,a.memo,a.distributorcode, " +
            		" a.distributortype,f.`name` distributortypeName,a.channelperson,a.natureofcompany,g.name as natureOfCompanyName, a.legalperson, " +
					" a.customer_source as customerSource, h.name as customerSourceName, " +
            		" a.establishmenttime,a.unifiedcreditcode,a.registeredcapital,a.scopeofbusiness,a.website,a.longitude,a.latitude " +
					" ,a.customer_owner_id AS customerOwnerId " +
					" from customer a " +
					" left join t_param b on a.customernature=b.`value` and b.param_type='20' " +
					" left join t_param c on a.businesstype=c.`value` and c.param_type='21' " +
					" left join t_param d on a.customerlevel=d.`value` and d.param_type='22' " +
					" left join t_xzqh e on a.xzqhdm=e.xzqhdm " +
					" left join t_param f on a.distributortype=f.`value` and f.param_type='23' " +
					" left join t_param g on a.natureofcompany=g.`value` and g.param_type='24' " +
					" left join t_param h on a.customer_source=h.`value` and h.param_type='13' " +
					" where a.isdelete=0 and a.uuid=?");

            List<Map<String, Object>> customerList = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{param.get("customerId")});
            extData.put("customerInfo",customerList.size()>0?customerList.get(0):null);

            //获取跟进记录
            sb.delete(0,sb.length());
            sb.append("select a.uuid,a.clueId,a.customerId,a.opportunityId,a.projectId,a.type,b.`name` typeName,a.userid,a.username,a.visittype,c.`name` visittypeName,a.visitaddress,a.visittime,a.visitcontent,a.createTime " +
					" from log_claim a " +
					" left join t_param b on a.type=b.`value` and b.param_type='18' " +
					" left join t_param c on a.visittype=c.`value` and c.param_type='19' " +
					" where a.isdelete=0 and a.type='02' and a.customerId=? " +
					" order by a.createTime desc");
            List<Map<String, Object>> claimList = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{param.get("customerId")});
            extData.put("claimList",claimList);

            //获取客户联系人
            sb.delete(0,sb.length());
            sb.append("select a.id,a.customer_id as customerId,b.customername,a.post,a.is_main ismain,a.name,a.sex,a.department,a.phone,a.wechat " +
					" from customer_staff a " +
					" left join customer b on a.customer_id=b.uuid " +
					" where a.deleted=0 and a.customer_id=?" +
					" order by a.gmt_create desc");
            List<Map<String, Object>> staffList = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{param.get("customerId")});
            extData.put("staffList",staffList);

            //获取商机列表
            sb.delete(0,sb.length());
            sb.append("select a.uuid,a.opportunityname,a.opportunitystage,c.`name` opportunitystageName,a.opportunitytype,d.`name` opportunitytypeName, " +
            		" a.opportunityowner,b.nickName opportunityownerName,a.projectaddress,a.projectarea,a.opportunitymoney,a.estimatedclosingdate " +
            		" from opportunity a " +
            		" left join t_user b on a.opportunityowner=b.id " +
            		" left join t_param c on a.opportunitystage=c.`value` and c.param_type='16' " +
            		" left join t_param d on a.opportunitytype=d.`value` and d.param_type='17' " +
            		" where a.isdelete=0 and a.customerId=? " +
            		" order by a.createTime desc ");
            List<Map<String, Object>> opportunityList = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{param.get("customerId")});
            extData.put("opportunityList",opportunityList);

            //获取文件列表
            sb.delete(0,sb.length());
            sb.append("select a.uuid,a.customerId,b.customername,a.filetype,c.`name` filetypeName,a.filename,a.filepath,a.memo,a.createUid,d.nickName,a.createTime " +
					" from customer_file a " +
            		" left join customer b on a.customerId=b.uuid " +
            		" left join t_param c on a.filetype=c.`value` and c.param_type='29' " +
            		" left join t_user d on a.createUid=d.id " +
            		" where a.isdelete=0 and customerId=? " +
            		" order by a.createTime desc");
            List<Map<String, Object>> fileList = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{param.get("customerId")});
            extData.put("fileList",fileList);
			
			//获取客户文件真实路径
            Iterator<Map<String, Object>> iterator = fileList.iterator();
            while(iterator.hasNext()){
            	Map<String, Object> next = iterator.next();
            	String filepath = (String) next.get("filepath");
            	String imgSignURL = OssUtil.getFileSignURL(filepath, 0);
            	next.put("realFilePath", imgSignURL);
            }
            
            //获取合同
            sb.delete(0,sb.length());
            sb.append(" ");
            List<Map<String, Object>> contractList = new ArrayList<Map<String,Object>>();//this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{param.get("customerId")});
            extData.put("contractList",contractList);

            res.setExtData(extData);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }
	
	public PageData saveCustomerStaff(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		newg_status = begin_newg();// 开启事物
		param.put("tableName", "customer_staff");
		param.put("isdelete", "0");
		//添加操作日志
		param.put("OperationLogType", "02");
		param.put("OperationLogId", "updateCustomerId");
		
		try {
			
			//如果传入是主要联系人的话 将其他该客户的联系人变为否
			if("01".equals(param.get("ismain"))){
				StringBuffer sb = new StringBuffer();
				sb.append("update customer_staff set  is_main='02' where customer_Id='").append(param.get("customerId")).append("' and id !=? ");
				this.getJdbcTemplate_newg().update(sb.toString(),new Object[]{param.get("id")});
				
			}
			
			String id=(String)param.get("id");
			if(StringUtils.isEmpty(id)) {
				param.put("OperationLogName", "增加客户联系人");
				param.put("OperationMemo", req.getUinfo().getNickName()+"添加了一条联系人信息");
				res =  insertTableData(newg_status, req) ;
			}else {
				param.put("keys", "id");
				param.put("OperationLogName", "修改客户联系人");
				param.put("OperationMemo", req.getUinfo().getNickName()+"修改了一条联系人信息");
				res =  updateTableData(newg_status, req) ;
			}
			
			if(res.getRetCode()==0) {
				commit_newg(newg_status);
				res.setRetCode(0);
				res.setRetMessage("OK");
				res.setKey((String)param.get("uuid")) ;
			}else{
				rollback_newg(newg_status);
				res.setRetCode(9);
				res.setRetMessage("error");
			}
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
		} finally {
			
		}
		return res;
	}
	
	@SuppressWarnings("unchecked")
    public PageData delCustomerStaff(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("客户联系人删除失败");
        newg_status = begin_newg();// 开启事物

        param.put("tableName", "customer_staff") ;
        param.put("keys", "uuid") ;
        param.put("isdelete", "1");
        try {
            PageData sqlInfo = getUpdateSQL(req);
            if (sqlInfo.getRetCode() != 0) {
                rollback_newg(newg_status);
                return sqlInfo;
            }
            //修改主表
            String  sb_field = "update customer_staff set deleted=1  where id=?";
            List<String> listData = (List<String>) sqlInfo.getData().get("listData");
            this.getJdbcTemplate_newg().update(sb_field.toString(),listData.toArray());

            param.put("OperationMemo", req.getUinfo().getNickName()+"删除了一条联系人信息");
            SaveOperatelog(req,"02","删除客户联系人信息",sb_field+"---"+listData,newg_status);
            commit_newg(newg_status);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
            rollback_newg(newg_status);

        } finally {
        	 
        }
        return res;
    }
	
	public PageData saveCustomerClaim(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		newg_status = begin_newg();// 开启事物
		param.put("tableName", "log_claim");
		param.put("isdelete", "0");
		param.put("type", "02");
		
		//添加操作日志
		param.put("OperationLogType", "02");
		param.put("OperationLogId", "updateCustomerId");
		
		try {
			
			String uuid=(String)param.get("uuid");
			if(StringUtils.isEmpty(uuid)) {
				param.put("OperationLogName", "增加客户跟进记录");
				param.put("OperationMemo", req.getUinfo().getNickName()+"添加了一条跟进记录");
				res =  insertTableData(newg_status, req) ;
			}else {
				param.put("keys", "uuid");
				param.put("OperationLogName", "修改客户跟进记录");
				param.put("OperationMemo", req.getUinfo().getNickName()+"修改了一条跟进记录");
				res =  updateTableData(newg_status, req) ;
			}
			
			if(res.getRetCode()==0) {
				commit_newg(newg_status);
				res.setRetCode(0);
				res.setRetMessage("OK");
				res.setKey((String)param.get("uuid")) ;
			}else{
				rollback_newg(newg_status);
				res.setRetCode(9);
				res.setRetMessage("error");
			}
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
		} finally {
			
		}
		return res;
	}
	
	@SuppressWarnings("unchecked")
    public PageData delCustomerClaim(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("跟进记录删除失败");
        newg_status = begin_newg();// 开启事物
        param.put("tableName", "log_claim");
     
        try {
        	param.put("OperationMemo", req.getUinfo().getNickName()+"删除了一条跟进记录");
        	param.put("uuid", param.get("claimId"));
        	delClaimlog(req,"02",newg_status);
            commit_newg(newg_status);
            res.setKey((String)param.get("uuid")) ;
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
            rollback_newg(newg_status);
        } finally {
        	
        }
        return res;
    }
	
	@SuppressWarnings("unchecked")
	public PageData addCustomerFile1(DataReq req,MultipartFile file) {
		Map<String, Object> param = req.getReqData();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		PageData res = new PageData();
		String isDelete="0";
		res.setRetCode(9);
		res.setRetMessage("客户文件添加失败");
		newg_status = begin_newg();// 开启事物
		param.put("tableName", "customer_file");
		param.put("filename",file.getOriginalFilename());
		param.put("createUid",req.getUinfo().getId());
		param.put("createTime",new Date());
		PageData sqlInfo = null ;
		param.put("keys", "uuid") ;
		try {
			UUID uuID = UUID.randomUUID();
			String uuid=uuID.toString();
			param.put("uuid", uuid);
			//上传文件
			res=uploadFile(req,file);
			if(res.getRetCode()!=0){
				rollback_newg(newg_status);
				return res;
			}
			Map<String, Object> map =res.getData();
			param.put("filename", map.get("fileName"));
			param.put("filepath", map.get("filePath"));
			System.out.println(map.get("fileName")+" -> "+map.get("filePath"));
			sqlInfo = getInsertSQL(req);
			if (sqlInfo.getRetCode() != 0) {
				rollback_newg(newg_status);
				return sqlInfo;
			}
			res = isExists(req);
			if(res.getRetCode()!=0) {
				rollback_newg(newg_status);
				return res ;
			}
			final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
			final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
			this.getJdbcTemplate_newg().update(sb_field.toString(),listData.toArray());
			
			param.put("OperationMemo", req.getUinfo().getNickName()+"添加一条客户文件");
			SaveOperatelog(req,"02","新增客户文件",sb_field+"---"+listData,newg_status);
			commit_newg(newg_status);
			res.setData(map);
			res.setKey(uuid) ;
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);

		} finally {
			
		}
		return res;
	}
	
	public PageData saveCustomerFile(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		newg_status = begin_newg();// 开启事物
		param.put("tableName", "customer_file");
		param.put("isdelete", "0");
		
		//添加操作日志
		param.put("OperationLogType", "02");
		param.put("OperationLogId", "updateCustomerId");
		
		try {
			
			String uuid=(String)param.get("uuid");
			if(StringUtils.isEmpty(uuid)) {
				param.put("OperationLogName", "增加客户文件");
				param.put("OperationMemo", req.getUinfo().getNickName()+"添加了一条客户文件");
				res =  insertTableData(newg_status, req) ;
			}else {
				param.put("keys", "uuid");
				param.put("OperationLogName", "修改客户文件");
				param.put("OperationMemo", req.getUinfo().getNickName()+"修改了一条客户文件");
				res =  updateTableData(newg_status, req) ;
			}
			
			if(res.getRetCode()==0) {
				commit_newg(newg_status);
				res.setRetCode(0);
				res.setRetMessage("OK");
				res.setKey((String)param.get("uuid")) ;
			}else{
				rollback_newg(newg_status);
				res.setRetCode(9);
				res.setRetMessage("error");
			}
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
		} finally {
			
		}
		return res;
	}
	
	@SuppressWarnings("unchecked")
    public PageData delCustomerFile(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("客户文件删除失败");
        newg_status = begin_newg();// 开启事物

        param.put("tableName", "customer_file") ;
        param.put("keys", "uuid") ;
        param.put("isdelete", "1");
        param.put("uuid", param.get("fileId"));
        try {
            PageData sqlInfo = getUpdateSQL(req);
            if (sqlInfo.getRetCode() != 0) {
                rollback_newg(newg_status);
                return sqlInfo;
            }
            //修改主表
            StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
            List<String> listData = (List<String>) sqlInfo.getData().get("listData");
            this.getJdbcTemplate_newg().update(sb_field.toString(),listData.toArray());

            param.put("OperationMemo", req.getUinfo().getNickName()+"删除了一条客户文件");
            SaveOperatelog(req,"02","删除客户文件信息",sb_field+"---"+listData,newg_status);
            commit_newg(newg_status);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
            rollback_newg(newg_status);

        } finally {
        	
        }
        return res;
    }
	
	public PageData uploadFile(DataReq req,MultipartFile file) {
		PageData res = new PageData();
		Map<String, Object> param = req.getReqData();
		res.setRetCode(9);
		res.setRetMessage("上传文件失败");
		String fileName="";
		String OriginalFilename="";
		try {
			if (file != null && file.getSize() > 0) {
				String prefix = ".doc";
				OriginalFilename=file.getOriginalFilename();
				if ( OriginalFilename != null) {
					prefix = OriginalFilename.substring(
							OriginalFilename.lastIndexOf("."));
				}
				fileName= "file/" + DBUitl.getDate(0) + "/file_"
						+ param.get("uuid") + "_"
						+ String.valueOf((new java.util.Date()).getTime())
						+ prefix;
				boolean isOK = DBUitl.SaveImageFromStream(
						file.getInputStream() , fileName);
				Map<String, Object> map=new HashMap<String, Object>();
				map.put("filePath", fileName);
				map.put("fileName", OriginalFilename);
				if (!isOK) {
					return res ;
				}else{
					res.setRetCode(0);
					res.setData(map);
					res.setRetMessage("OK");
				}
			}else{
				res.setRetCode(9);
				res.setRetMessage("上传文件失败");
			}

		} catch (Exception e) {
			e.printStackTrace();
			res.setRetCode(9);
			res.setRetMessage("上传文件失败");
		} finally {

		}
		return res;
	}
	
	@SuppressWarnings("unchecked")
    public PageData delCustomer(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("客户删除失败");
        newg_status = begin_newg();// 开启事物

        param.put("tableName", "customer") ;
        param.put("keys", "uuid") ;
        param.put("isdelete", "1");
        
        if(isCustomerInOpportunity(String.valueOf(param.get("uuid")))){
        	res.setRetCode(9);
            res.setRetMessage("此客户已加入商机");
            return res;
        }
        try {
            PageData sqlInfo = getUpdateSQL(req);
            if (sqlInfo.getRetCode() != 0) {
                rollback_newg(newg_status);
                return sqlInfo;
            }
            //修改主表
            StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
            List<String> listData = (List<String>) sqlInfo.getData().get("listData");
            this.getJdbcTemplate_newg().update(sb_field.toString(),listData.toArray());
            
            param.put("customerId", param.get("uuid"));
            param.put("OperationMemo", req.getUinfo().getNickName()+"删除了客户"+param.get("customerName"));
            SaveOperatelog(req,"02","删除客户信息",sb_field+"---"+listData,newg_status);
            commit_newg(newg_status);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
            rollback_newg(newg_status);

        } finally {
        	
        }
        return res;
    }
	
	//判断客户是否关联商机
	public boolean isCustomerInOpportunity(String customerId) {
		List<Map<String, Object>> list = null;
		boolean result = false;
        try {
            StringBuffer sb = new StringBuffer();
            sb.delete(0,sb.length());
            sb.append("select count(1) cnt from opportunity oppo where oppo.isdelete=0 and oppo.customerId=?");

            list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{customerId});
            if(Integer.parseInt(String.valueOf(list.get(0).get("cnt")))>0){
            	result = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return result;
    }
	public PageData getCustomerDynamic(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        Map<String,Object> extData = new LinkedHashMap<String, Object>();
        res.setRetCode(9);
        res.setRetMessage("获取动态失败");
        try {
            StringBuffer sb = new StringBuffer();
            StringBuffer sbform = new StringBuffer();
            
            sbform.delete(0, sbform.length());
            if(!StringUtils.isEmpty((String) param.get("customerId"))){
            	sbform.append(" and a.customerId='").append(param.get("customerId")).append("' ");
            }else if(!StringUtils.isEmpty((String) param.get("clueId"))){
            	sbform.append(" and a.clueId='").append(param.get("clueId")).append("' ");
            }else if(!StringUtils.isEmpty((String) param.get("opportunityId"))){
            	sbform.append(" and a.opportunityId='").append(param.get("opportunityId")).append("' ");
            }else{
            	sbform.append(" and 1=0 ");
            }
            //获取客户动态
            sb.delete(0,sb.length());
            sb.append("select * " +
					" from ( " +
					" select a.uuid,'02' type,a.createUid operUserId,h.nickName operUserName,a.type opertype, " +
					" d.cluename clueName,d.cluephone,e.customername customerName,f.opportunityname opportunityName,g.project_name projectName, " +
					" a.createTime operTime,a.username visitUserName,a.visittype visitType,c.`name` visitTypeName, " +
					" a.visitaddress visitAddress,a.visittime visitTime,a.visitcontent visitContent,'' remark,'' memo " +
					" from log_claim a " +
					" left join t_param b on a.type=b.`value` and b.param_type='18' " +
					" left join t_param c on a.visittype=c.`value` and c.param_type='19' " +
					" left join clue d on a.clueId=d.uuid " +
					" left join customer e on a.customerId=e.uuid " +
					" left join opportunity f on a.opportunityId=f.uuid " +
					" left join t_project g on a.projectId=g.uuid " +
					" left join t_user h on a.createUid=h.id " +
					" where a.isdelete=0 "); 
            		sb.append(sbform.toString());
					sb.append(" union  " +
					" select a.uuid,'01' type,a.userid operUserId,g.nickName operUserName, " +
					" a.type opertype,c.cluename clueName,c.cluephone,d.customername customerName,e.opportunityname opportunityName,f.project_name projectName, " +
					" a.createTime operTime,'' visitusername,'' visittype,'' visittypeName,'' visitaddress,'' visittime,'' visitcontent, " +
					" a.remark,a.memo " +
					" from log_operate a " +
					" left join t_param b on a.type=b.`value` and b.param_type='18' " +
					" left join clue c on a.clueId=c.uuid " +
					" left join customer d on a.customerId=d.uuid " +
					" left join opportunity e on a.opportunityId=e.uuid " +
					" left join t_project f on a.projectId=f.uuid " +
					" left join t_user g on a.userid=g.id " +
					" where 1=1 ");
            		sb.append(sbform.toString());
					sb.append(" ) l " +
					" order by l.operTime desc");

            List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});

            res.setExtData(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }
	
	public PageData getCustomerStaff(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取客户联系人列表失败");
        try {
            StringBuffer sb = new StringBuffer();
            StringBuffer sbfrom = new StringBuffer() ;
            sb.append("select a.uuid,a.customerId,b.customername,a.department,a.post,a.name,a.sex,a.phone,a.ismain,a.wechat " +
					" from customer_staff a " +
            		" left join customer b on a.customerId=b.uuid " +
            		" where a.isdelete=0 and a.customerid='").append(param.get("customerId")).append("' ");
            
            sb.append("  order by a.createTime desc ");
            sb = DBUtils.sql_check(sb) ;
            sbfrom.append(sb.toString()) ;
            int idx=sbfrom.indexOf(" from ") ;
            sbfrom.delete(0, idx);
            Page page = new Page(this.getJdbcTemplate_newg(),sbfrom.toString(),req.getPageid(),req.getPagesize(),res,new Object[]{}) ;
            sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize()) ;
            List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }
}
